package com.zmn.plat.common.util.excel;

import com.zmn.plat.common.exception.PlatException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author fxc
 * @date 2021/3/26  17:07
 * @description excel导入工具类
 */
public class ExcelImportUtil {

    /**
     * 创建WorkBook对象
     *
     * @param filePath
     * @return
     * @throws IOException
     */
    public static Workbook createWorkbook(String filePath) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            throw new PlatException("文件不存在");
        }

        if (filePath.trim().toLowerCase().endsWith("xls")) {
            return new XSSFWorkbook(new FileInputStream(filePath));
        } else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
            return new XSSFWorkbook(new FileInputStream(filePath));
        } else {
            throw new PlatException("文件类型错误");
        }
    }

    /**
     * 获取Sheet页面(按名称)
     *
     * @param wb
     * @param sheetName
     * @return
     */
    public static Sheet getSheet(Workbook wb, String sheetName) {
        return wb.getSheet(sheetName);
    }

    /**
     * 获取Sheet页面(按页标)
     *
     * @param wb
     * @param index
     * @return
     */
    public static Sheet getSheet(Workbook wb, int index) {
        return wb.getSheetAt(index);
    }

    /**
     * 获取Sheet页内容
     *
     * @param sheet
     * @return
     */
    public static List<String[]> listFromSheet(Sheet sheet) {
        List<String[]> list = new ArrayList<>();
        for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null || row.getPhysicalNumberOfCells() == 0) {
                continue;
            }
            String[] cells = new String[row.getLastCellNum()];
            for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                }
                cell.setCellType(CellType.STRING);
                cells[c] = cell.getStringCellValue();
            }
            list.add(cells);
        }
        return list;
    }



    /**
     * 根据Sheet页导入Excel信息
     *
     * @param filePath   文件路径
     * @param sheetIndex Sheet页下标
     * @param startRow   开始列 ：默认第一列
     * @param startLine  开始行 ：默认第一行
     * @throws Exception
     */
    public static List<Object[]> importExcelBySheetIndex(String filePath, int sheetIndex
            , int startRow, int startLine) throws Exception {
        List<Object[]> resultList = null;
        //创建WorkBook对象
        Workbook wb = createWorkbook(filePath);
        // 获取Sheet
        Sheet sheet = getSheet(wb, sheetIndex);
        // 判断Sheet是否为空
        if (sheet != null) {
            // 遍历Sheet
            List<String[]> list = listFromSheet(sheet);
            if (list != null && list.size() > 0) {
                resultList = new ArrayList<>();
                if (startLine <= list.size()) {
                    for (int i = startLine; i < list.size(); i++) {
                        int nullCount = 0;
                        Object[] rows = list.get(i);
                        if (rows != null && rows.length > 0) {
                            List<Object> resultObjects = new ArrayList<>();
                            for (int n = startRow; n < rows.length; n++) {
                                if (isEmpty(rows[n])) {
                                    nullCount++;
                                }
                                resultObjects.add(rows[n]);
                            }
                            //判断空的单元格个数
                            if (nullCount >= rows.length) {
                                break;
                            } else {
                                resultList.add(resultObjects.toArray());
                            }
                        }
                    }
                }
            }
        }
        return resultList;
    }

    /**
     * 对象是否为空
     *
     * @param o String,List,Map,Object[],int[],long[]
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static boolean isEmpty(Object o) {
        if (o == null) {
            return true;
        }
        if (o instanceof String) {
            if (o.toString().trim().equals("")) {
                return true;
            }
            return o.equals("null") || o.equals("NULL");
        } else if (o instanceof List) {
            return ((List) o).size() == 0;
        } else if (o instanceof Map) {
            return ((Map) o).size() == 0;
        } else if (o instanceof Set) {
            return ((Set) o).size() == 0;
        } else if (o instanceof Object[]) {
            return ((Object[]) o).length == 0;
        } else if (o instanceof int[]) {
            return ((int[]) o).length == 0;
        } else if (o instanceof long[]) {
            return ((long[]) o).length == 0;
        }
        return false;
    }
}
